#! /usr/bin/env python3
# -*- coding: utf-8 -*-

import tkinter as tk
from tkinter import Tk
from tkinter.filedialog import askopenfilename
import openpyxl
import os
from openpyxl.utils import get_column_letter, column_index_from_string
import logging
from easygui import *

logging.basicConfig(
    level = logging.DEBUG,
    format = '%(asctime)s %(levelname)-5s - %(message)s'
)

# Tk().withdraw() # we don't want a full GUI, so keep the root window from appearing
# filename = askopenfilename() # show an "Open" dialog box and return the path to the selected file
# print(filename)

NAMES = ['Simon', 'Eagle', 'Paul', 'George', 'Jim', 'Leo', 'Rocky', 'Tony']

source_dir_label = None
target_file_label = None
result_label = None
month_label = None

months = None
source_dir = None
target_filename = None

def select_months():
    global months
    global month_label
    choices = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
    msg = '选择需要处理的月份：'
    months = multchoicebox(msg, "选择月份", choices)
    prompt = "已选月份: {}".format(months)
    month_label.config(text=prompt)


def select_source_dir():
    global source_dir
    global source_dir_label
    #dirname = askopenfilename()
    source_dir = diropenbox()
    prompt = "数据目录: {}".format(source_dir)
    source_dir_label.config(text=prompt)


def select_target_file():
    global target_filename
    global target_file_label
    target_filename = askopenfilename()
    prompt = "汇总文件: {}".format(target_filename)
    target_file_label.config(text=prompt)


def _get_month_col(month, iter):
    for c in iter:
        if c.value == month:
            return c.column


def _get_col_plus(col):
    return get_column_letter(column_index_from_string(col) + 1)

def _get_name_from_filename(filename):
    for name in NAMES:
        if name.lower() in filename.lower():
            return name

    raise Exception("No name found")


def _get_sheet_name_from_person_name(wb, name):
    for sheetname in wb.sheetnames:
        if name.lower() in sheetname.lower():
            return sheetname

    raise Exception("No sheetname found")

def _copy_sheet(source_sheet, target_sheet, month):
    second_row = list(source_sheet.rows)[1]
    month_col = _get_month_col(month, second_row)
    logging.info("month col for {}: {}".format(month, month_col))
    for c in list(source_sheet.columns)[2]:
        if c.value is not None and '预测' in c.value:
            r = c.row
            coord = '{}{}'.format(month_col, r)
            coord2 = '{}{}'.format(_get_col_plus(month_col), r)
            v = source_sheet[coord].value
            v_remark = source_sheet[coord2].value

            if v is not None:
                target_sheet[coord] = v
            if v_remark is not None:
                target_sheet[coord2] = v_remark


def handle_one_source_file(absolute_item_filename, target_wb, months):
    basename = os.path.basename(absolute_item_filename)
    person_name = _get_name_from_filename(basename)
    source_wb = openpyxl.load_workbook(absolute_item_filename)
    sheetname = _get_sheet_name_from_person_name(source_wb, person_name)
    source_sheet = source_wb[sheetname]
    target_sheet = target_wb[sheetname]
    for month in months:
        logging.info("Handling sheet copy for {}, {}".format(person_name, month))
        _copy_sheet(source_sheet, target_sheet, month)


def process():
    global result_label
    global months
    global source_dir
    global target_filename
    logging.info("Processing month: {}".format(months))
    files = os.listdir(source_dir)
    logging.info("Processing files: {}".format(files))
    try:
        target_wb = openpyxl.load_workbook(target_filename)
        for f in files:
            absolute_filename = "{}/{}".format(source_dir, f)
            logging.info("Handling {}".format(absolute_filename))
            handle_one_source_file(absolute_filename, target_wb, months)


        basename = os.path.basename(target_filename)
        target_dir = os.path.dirname(target_filename)
        result_filename = 'Result-{}'.format(basename)
        absolute_result_filename = os.path.join(target_dir, result_filename)
        # logging.info("target_dir: {}".format(target_dir))
        # logging.info("basename: {}".format(basename))
        logging.info("Saving destination excel file: {}".format(absolute_result_filename))
        target_wb.save(absolute_result_filename)
        result_label.config(text='Result file saved at: {}'.format(absolute_result_filename))
    except Exception as ex:
        logging.error("Error: {}".format(str(ex)))
        result_label.config(text=str(ex), fg='red')




def process0():
    global result_label
    global choice
    # result_label_v.set("Processing...")

    month = choice.get()
    logging.info("select month: {}".format(month))
    wb = openpyxl.load_workbook(source_filename)
    target_wb = openpyxl.load_workbook(target_filename)

    sheet = wb['Eagle']
    target_sheet = target_wb['Eagle']

    second_row = list(sheet.rows)[1]
    month_col = _get_month_col(month, second_row)
    logging.info("month col: {}".format(month_col))

    for c in list(sheet.columns)[2]:
        if c.value is not None and '预测' in c.value:
            # logging.info(c.value)
            r = c.row
            # logging.info((r, month_col))
            # logging.info(sheet['{}{}'.format(month_col, r)].value)
            coord = '{}{}'.format(month_col, r)
            coord2 = '{}{}'.format(_get_col_plus(month_col), r)
            v = sheet[coord].value
            v2 = sheet[coord2].value

            if v is not None:
                target_sheet[coord] = v
            if v2 is not None:
                target_sheet[coord2] = v2

    target_wb.save("/tmp/tmp.xlsx")
    result_label.config(text='Result: OK')



def _config_root(root):
    w = 700 # width for the Tk root
    h = 150 # height for the Tk root

    # get screen width and height
    ws = root.winfo_screenwidth() # width of the screen
    hs = root.winfo_screenheight() # height of the screen

    # calculate x and y coordinates for the Tk root window
    x = (ws/2) - (w/2)
    y = (hs/2) - (h/2)

    # set the dimensions of the screen
    # and where it is placed
    root.geometry('%dx%d+%d+%d' % (w, h, x, y))
    root.title("数据汇总")



def init_buttons(frame):
    tk.Button(frame, text="选择月份", command=select_months).pack(side=tk.LEFT)
    tk.Button(frame, text="数据目录", command=select_source_dir).pack(side=tk.LEFT)
    tk.Button(frame, text="汇总文件", command=select_target_file).pack(side=tk.LEFT)
    tk.Button(frame, text="处理", command=process).pack(side=tk.LEFT)
    tk.Button(frame, text="退出", command=quit).pack(side=tk.LEFT)

def init_labels(root):
    global source_dir_label
    global target_file_label
    global result_label
    global month_label

    month_label = tk.Label(root)
    month_label.pack()

    source_dir_label = tk.Label(root)
    source_dir_label.pack()

    target_file_label = tk.Label(root)
    target_file_label.pack()

    result_label = tk.Label(root)
    result_label.pack()




def init_panel():
    root = Tk()
    _config_root(root)

    frame = tk.Frame(root)
    frame.pack()

    init_buttons(frame)
    init_labels(root)

    root.mainloop()



if __name__ == '__main__':
    init_panel()
